#include "recordservice.h"
#include <qsqlquery.h>
#include <QDateTime>
#include "autoDatabase.h"

RecordService::RecordService()
{

}

bool RecordService::createTable()
{
    // 获取数据连接
    AutoDatabase autoDb;

    // 数据库查询
    QSqlQuery query(autoDb.getDb());
    return query.exec(QString("CREATE TABLE IF NOT EXISTS t_record ("
                              "`id` varchar(32) NOT NULL COMMENT '记录编码',"
                              "`userId` varchar(32) NOT NULL COMMENT '用户编码',"
                              "`name` varchar(32) NOT NULL COMMENT '用户姓名',"
                              "`gender` int DEFAULT 0 COMMENT '用户性别',"
                              "`idCard` varchar(32) DEFAULT NULL COMMENT '证件号码',"
                              "`image` varchar(255) DEFAULT NULL COMMENT '人脸路径',"
                              "`upload` int DEFAULT 0 COMMENT '上传状态',"
                              "`uploadCount` int DEFAULT 0 COMMENT '上传次数',"
                              "`uploadTime` datetime default NULL COMMENT '上传时间',"
                              "`createTime` datetime default now() COMMENT '添加时间',"
                              "`reserver1` varchar(128) default NULL COMMENT '保留字段',"
                              "`reserver2` varchar(128) default NULL COMMENT '保留字段',"
                              "primary key(`id`),"
                              "key `idx_record_user`(`userId`, `createTime`)"
                              ") ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='刷卡记录';"));
}

bool RecordService::add(RecordInfo record)
{
    // 获取数据连接
    AutoDatabase autoDb;

    // 数据库查询
    QString sql = "insert into t_record(`id`, `userId`, `name`, `gender`, `idCard`,"
                  "`image`, `upload`, `uploadCount`, `uploadTime`, `createTime`)"
                  "values(:id, :userId, :name, :gender, :idCard, :image,"
                  ":upload, :uploadCount, :uploadTime, :createTime)";
    QSqlQuery query(autoDb.getDb());
    query.prepare(sql);
    query.bindValue(":id", record.id);
    query.bindValue(":userId", record.userId);
    query.bindValue(":name", record.name);
    query.bindValue(":gender", record.gender);
    query.bindValue(":idCard", record.idCard);
    query.bindValue(":image", record.image);
    query.bindValue(":upload", record.upload);
    query.bindValue(":uploadCount", record.uploadCount);
    query.bindValue(":uploadTime", record.uploadTime);
    query.bindValue(":createTime", record.createTime);
    return query.exec();
}

bool RecordService::remove(QString id)
{
    // 获取数据连接
    AutoDatabase autoDb;

    // 移除数据记录
    QString sql = "delete from t_record where `id`=:id";
    QSqlQuery query(autoDb.getDb());
    query.prepare(sql);
    query.bindValue(":id", id);
    return query.exec();
}

bool RecordService::update(RecordInfo record)
{
    // 获取数据连接
    AutoDatabase autoDb;

    // 更新数据记录
    QString sql = "update t_record set `userId`=:userId, `name`=:name, `gender`=:gender,"
                  "`idCard`=:idCard, `image`=:image, `upload`=:upload, `uploadCount`=:uploadCount,"
                  "`uploadTime`=:uploadTime, `createTime`=:createTime where id=:id";
    QSqlQuery query(autoDb.getDb());
    query.prepare(sql);
    query.bindValue(":userId", record.userId);
    query.bindValue(":name", record.name);
    query.bindValue(":gender", record.gender);
    query.bindValue(":idCard", record.idCard);
    query.bindValue(":image", record.image);
    query.bindValue(":upload", record.upload);
    query.bindValue(":uploadCount", record.uploadCount);
    query.bindValue(":uploadTime", record.uploadTime);
    query.bindValue(":createTime", record.createTime);
    query.bindValue(":id", record.id);
    return query.exec();
}

bool RecordService::find(QString userId, QVector<RecordInfo>& records)
{
    // 获取数据连接
    AutoDatabase autoDb;

    // 查询数据记录
    QString sql = "select * from t_record where `userId`=:userId";
    QSqlQuery query(autoDb.getDb());
    query.prepare(sql);
    query.bindValue(":userId", userId);

    // 打印查询结果
    if (query.exec()) {
        while (query.next()) {
            RecordInfo record;
            record.id = query.value("id").toString();
            record.userId = query.value("userId").toString();
            record.name = query.value("name").toString();
            record.gender = query.value("gender").toInt();
            record.idCard = query.value("idCard").toString();
            record.image = query.value("image").toString();
            record.upload = query.value("upload").toInt();
            record.uploadCount = query.value("uploadCount").toInt();
            record.uploadTime = query.value("uploadTime").toDateTime().toString("yyyy-MM-dd hh:mm:ss");
            record.createTime = query.value("createTime").toDateTime().toString("yyyy-MM-dd hh:mm:ss");
            records.push_back(record);
        }
        return true;
    }
    return false;
}

bool RecordService::findAll(QVector<RecordInfo>& records)
{
    // 获取数据连接
    AutoDatabase autoDb;

    // 查询数据记录
    QString sql = "select * from t_record";
    QSqlQuery query(autoDb.getDb());
    query.prepare(sql);

    // 执行结果查询
    bool success = query.exec();
    if (success) {
        // 打印查询结果
        while (query.next()) {
            RecordInfo record;
            record.id = query.value("id").toString();
            record.userId = query.value("userId").toString();
            record.name = query.value("name").toString();
            record.gender = query.value("gender").toInt();
            record.idCard = query.value("idCard").toString();
            record.image = query.value("image").toString();
            record.upload = query.value("upload").toInt();
            record.uploadCount = query.value("uploadCount").toInt();
            record.uploadTime = query.value("uploadTime").toDateTime().toString("yyyy-MM-dd hh:mm:ss");
            record.createTime = query.value("createTime").toDateTime().toString("yyyy-MM-dd hh:mm:ss");
            records.push_back(record);
        }
    }
    return success;
}
